﻿using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

using Com.Ompchina.Lib.DBHelper;
using IDAL.Cms;
using IDAL;
using Model.Cms;
using Model.Config;
using Model.Base;
namespace SQLServerDAL.Cms
{
    /// <summary>
    /// 数据层，内容管理，区块管理
    /// </summary>
    public class BlockDAL : DalBase, IBlock
    {
        public BlockDAL() 
        {
            Schema = "cms.";
            TableName = Schema + "Block";
            ViewTableName = Schema + "V_Block";
            Fields = "BlockID, BlockType, BlockName, BlockSize, Link, LinkTarget, Filepath, Content, Rank, IsOnline, OnlineTime, UpdateTime, AddTime, BlockCategoryID, BlockCategoryName, BlockCategoryLabel";
        }

        #region IBlock 成员
        /// <summary>
        /// 新增广告
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public int InsertBlock(BlockInfo info)
        {
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@BlockCategory",SqlDbType.Int)
                ,new SqlParameter("@BlockType",SqlDbType.Char,1)
                ,new SqlParameter("@BlockName",SqlDbType.NVarChar,20)
                ,new SqlParameter("@BlockSize",SqlDbType.VarChar,10)
                ,new SqlParameter("@Link",SqlDbType.NVarChar,200)
                ,new SqlParameter("@LinkTarget",SqlDbType.VarChar,10)
                ,new SqlParameter("@Filepath",SqlDbType.VarChar,100)                
                ,new SqlParameter("@Content",SqlDbType.NVarChar,2000)
                ,new SqlParameter("@Rank",SqlDbType.Int)
                ,new SqlParameter("@StaffID",SqlDbType.Int)
                ,new SqlParameter("@IsOnline",SqlDbType.Char,1)
                ,new SqlParameter("@OnlineTime",SqlDbType.DateTime)
                ,new SqlParameter("@RtnCode",SqlDbType.Char,2)
            };
            int i = 0;
            parm[i++].Value = info.BlockCategory.TermID;
            parm[i++].Value = info.BlockType;
            parm[i++].Value = info.BlockName;
            parm[i++].Value = info.BlockSize;
            parm[i++].Value = info.Link;
            parm[i++].Value = info.LinkTarget;
            parm[i++].Value = info.Filepath;
            parm[i++].Value = info.Content;
            parm[i++].Value = info.Rank;
            parm[i++].Value = info.Staff.StaffID;
            parm[i++].Value = info.IsOnline;
            parm[i++].Value = info.OnlineTime;
            parm[i++].Direction = ParameterDirection.Output;
            return SqlHelper.ExecuteNonQuery(base.ConnectionString, CommandType.StoredProcedure
                , Schema + "[AddBlock]", parm);
        }
        /// <summary>
        /// 更新广告
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool UpdateBlock(BlockInfo info)
        {
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@BlockID",SqlDbType.Int)
                ,new SqlParameter("@BlockCategory",SqlDbType.Int)
                ,new SqlParameter("@BlockType",SqlDbType.Char,1)
                ,new SqlParameter("@BlockName",SqlDbType.NVarChar,20)
                ,new SqlParameter("@BlockSize",SqlDbType.VarChar,10)
                ,new SqlParameter("@Link",SqlDbType.NVarChar,200)
                ,new SqlParameter("@LinkTarget",SqlDbType.VarChar,10)
                ,new SqlParameter("@Filepath",SqlDbType.VarChar,100)                
                ,new SqlParameter("@Content",SqlDbType.NVarChar,2000)
                ,new SqlParameter("@Rank",SqlDbType.Int)
                ,new SqlParameter("@StaffID",SqlDbType.Int)
                ,new SqlParameter("@IsOnline",SqlDbType.Char,1)
                ,new SqlParameter("@OnlineTime",SqlDbType.DateTime)
                ,new SqlParameter("@RtnCode",SqlDbType.Char,2)
            };
            int i = 0;
            parm[i++].Value = info.BlockID;
            parm[i++].Value = info.BlockCategory.TermID;
            parm[i++].Value = info.BlockType;
            parm[i++].Value = info.BlockName;
            parm[i++].Value = info.BlockSize;
            parm[i++].Value = info.Link;
            parm[i++].Value = info.LinkTarget;
            parm[i++].Value = info.Filepath;
            parm[i++].Value = info.Content;
            parm[i++].Value = info.Rank;
            parm[i++].Value = info.Staff.StaffID;
            parm[i++].Value = info.IsOnline;
            parm[i++].Value = info.OnlineTime;
            parm[i++].Direction = ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(base.ConnectionString, CommandType.StoredProcedure
                , Schema + "UpdateBlock", parm);
            return parm[parm.Length - 1].Value.ToString().Equals("80");
        }

        /// <summary>
        /// 删除广告
        /// </summary>
        /// <param name="blockID"></param>
        /// <param name="blockList"></param>
        /// <param name="staffID"></param>
        /// <returns></returns>
        public bool DeleteBlock(int blockID, string blockList, int staffID)
        {
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@BlockID",SqlDbType.Int)
                ,new SqlParameter("@BlockList",SqlDbType.VarChar,100)
                ,new SqlParameter("@RtnCode",SqlDbType.Char,2)
            };
            int i = 0;
            parm[i++].Value = blockID;
            parm[i++].Value = blockList;
            parm[i++].Direction = ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(base.ConnectionString, CommandType.StoredProcedure, "dbo.DeleteBlock", parm);
            return parm[parm.Length - 1].Value.ToString().Equals("80");
        }

        /// <summary>
        /// 根据编号获取广告位信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public BlockInfo GetBlockByID(int id)
        {
            Sql = string.Format("SELECT TOP 1 {0} FROM {1} WHERE BlockID = {2}"
                , Fields, ViewTableName, id);
            SqlDataReader dr = SqlHelper.ExecuteReader(base.ConnectionString, CommandType.Text, Sql);
            BlockInfo info = new BlockInfo();
            if (dr != null)
            {
                if (dr.Read())
                {//BlockID, BlockType, BlockName, BlockSize, Link, LinkTarget, Filepath, Content, Rank, IsOnline, OnlineTime, UpdateTime, AddTime, BlockCategoryID, BlockCategoryName, BlockCategoryLabel
                    int i = 0;
                    info.BlockID = dr.GetInt32(i++);
                    info.BlockType = dr.GetString(i++);
                    info.BlockName = dr.GetString(i++);
                    info.BlockSize = dr.GetString(i++);
                    info.Link = dr.GetString(i++);
                    info.LinkTarget = dr.GetString(i++);
                    info.Filepath = dr.GetString(i++);
                    info.Content = dr.GetString(i++);
                    info.Rank = dr.GetInt32(i++);
                    info.IsOnline = dr.GetString(i++);
                    info.OnlineTime = dr.GetDateTime(i++);
                    info.UpdateTime = dr.GetDateTime(i++);
                    info.AddTime = dr.GetDateTime(i++);
                    info.BlockCategory = new TermInfo();
                    info.BlockCategory.TermID = dr.GetInt32(i++);
                    info.BlockCategory.TermName = dr.GetString(i++);
                    info.BlockCategory.TermLabel = dr.GetString(i++);
                }
                dr.Close();
            }
            return info;
        }

        /// <summary>
        /// 广告列表
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public IList<BlockInfo> ListBlock(BlockCondition condition)
        {
            SqlBuilder = new System.Text.StringBuilder("SELECT ");
            if (condition.Top > 0)
                SqlBuilder.AppendFormat(" TOP {0} ", condition.Top);
            SqlBuilder.AppendFormat(" {0} FROM {1} WHERE 1 = 1", Fields, ViewTableName);
            if (condition.BlockCategoryID > 0)
                SqlBuilder.AppendFormat(" AND BlockCategoryID = {0}", condition.BlockCategoryID);
            if (!string.IsNullOrEmpty(condition.BlockCategoryLabel))
                SqlBuilder.AppendFormat(" AND BlockCategoryLabel = '{0}'", condition.BlockCategoryLabel);
            if (!string.IsNullOrEmpty(condition.BlockType))
                SqlBuilder.AppendFormat(" AND BlockType = '{0}'", condition.BlockType);
            if (!string.IsNullOrEmpty(condition.IsOnline))
                SqlBuilder.AppendFormat(" AND IsOnline = '{0}'", condition.IsOnline);
            if (condition.TimeType.Equals(ListTime.Online))
                SqlBuilder.Append(" AND DATEDIFF(mi, OnlineTime, getdate()) > 0 ");
            if (condition.RangeType.Equals(ListRange.Range))
                SqlBuilder.AppendFormat(" AND OnlineTime BETWEEN '{0}' AND DATEADD(d, 1, '{1}') ", condition.StartTime, condition.StopTime);
            if (!string.IsNullOrEmpty(condition.OrderBy))
                SqlBuilder.AppendFormat(" ORDER BY " + condition.OrderBy);
            List<BlockInfo> list = new List<BlockInfo>();
            SqlDataReader dr = SqlHelper.ExecuteReader(base.ConnectionString, CommandType.Text, SqlBuilder.ToString());
            if (dr != null)
            {
                while (dr.Read())
                {
                    BlockInfo info = new BlockInfo();
                    int i = 0;
                    info.BlockID = dr.GetInt32(i++);
                    info.BlockType = dr.GetString(i++);
                    info.BlockName = dr.GetString(i++);
                    info.BlockSize = dr.GetString(i++);
                    info.Link = dr.GetString(i++);
                    info.LinkTarget = dr.GetString(i++);
                    info.Filepath = dr.GetString(i++);
                    info.Content = dr.GetString(i++);
                    info.Rank = dr.GetInt32(i++);
                    info.IsOnline = dr.GetString(i++);
                    info.OnlineTime = dr.GetDateTime(i++);
                    info.UpdateTime = dr.GetDateTime(i++);
                    info.AddTime = dr.GetDateTime(i++);
                    info.BlockCategory = new TermInfo();
                    info.BlockCategory.TermID = dr.GetInt32(i++);
                    info.BlockCategory.TermName = dr.GetString(i++);
                    info.BlockCategory.TermLabel = dr.GetString(i++);
                    list.Add(info);
                }
                dr.Close();
            }
            return list;
        }


        /// <summary>
        /// 广告列表，分页
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public IList<BlockInfo> ListBlock(BlockCondition condition, out int recordCount)
        {
            Wheres = new System.Text.StringBuilder(" 1 = 1 ");
            if (condition.BlockCategoryID > 0)
                Wheres.AppendFormat(" AND BlockCategoryID = {0}", condition.BlockCategoryID);
            if (!string.IsNullOrEmpty(condition.BlockCategoryLabel))
                Wheres.AppendFormat(" AND BlockCategoryLabel = '{0}'", condition.BlockCategoryLabel);
            if (!string.IsNullOrEmpty(condition.BlockType))
                Wheres.AppendFormat(" AND BlockType = '{0}'", condition.BlockType);
            if (!string.IsNullOrEmpty(condition.IsOnline))
                Wheres.AppendFormat(" AND IsOnline = '{0}'", condition.IsOnline);
            if (condition.TimeType.Equals(ListTime.Online))
                Wheres.Append(" AND DATEDIFF(mi, OnlineTime, getdate()) > 0 ");
            if (condition.RangeType.Equals(ListRange.Range))
                Wheres.AppendFormat(" AND OnlineTime BETWEEN '{0}' AND DATEADD(d, 1, '{1}') ", condition.StartTime, condition.StopTime);
            recordCount = base.PageListCounter(ViewTableName, Wheres.ToString());
            List<BlockInfo> list = new List<BlockInfo>();
            SqlDataReader dr = base.PageListRecord(Fields
                , ViewTableName, Wheres.ToString(), condition.OrderBy, condition.PageIndex, condition.PageSize);
            if (dr != null)
            {
                while (dr.Read())
                {
                    BlockInfo info = new BlockInfo();
                    int i = 0;
                    info.BlockID = dr.GetInt32(i++);
                    info.BlockType = dr.GetString(i++);
                    info.BlockName = dr.GetString(i++);
                    info.BlockSize = dr.GetString(i++);
                    info.Link = dr.GetString(i++);
                    info.LinkTarget = dr.GetString(i++);
                    info.Filepath = dr.GetString(i++);
                    info.Content = dr.GetString(i++);
                    info.Rank = dr.GetInt32(i++);
                    info.IsOnline = dr.GetString(i++);
                    info.OnlineTime = dr.GetDateTime(i++);
                    info.UpdateTime = dr.GetDateTime(i++);
                    info.AddTime = dr.GetDateTime(i++);
                    info.BlockCategory = new TermInfo();
                    info.BlockCategory.TermID = dr.GetInt32(i++);
                    info.BlockCategory.TermName = dr.GetString(i++);
                    info.BlockCategory.TermLabel = dr.GetString(i++);
                    list.Add(info);
                }
                dr.Close();
            }
            return list;
        }

        #endregion
    }
}
